package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import structure.Rodove;

/*Provide methods to work with db, like insertion, selecting etc.*/
public class DbHelper {

    private DbWorker db;
    private String main_select = "Select Rodove.id as id, Rodove.name as name, "
	    + "Rodove.place, Rodove.skhema, Rodove.year_start, Rodove.year_used, "
	    + "Oblast.name as oblast, Region.name as region, "
	    + "RodoweSizeGas.name as gas_size, "
	    + "RodoweSizeOil.name as oil_size, "
	    + "RodoweType.name as rodowe_type from Rodove "
	    + "left join Oblast on Oblast.id =Rodove.oblast_id "
	    + "left  join Region on Region.id =Rodove.region_id "
	    + "left join RodoweSizeGas on RodoweSizeGas.id =Rodove.oil_size_id "
	    + "left join RodoweSizeOil on RodoweSizeOil.id =Rodove.gas_size_id "
	    + "left join RodoweType on RodoweType.id =Rodove.type_id";

    /* contructor */
    public DbHelper() {
	try {
	    this.db = new DbWorker();
	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
    }

    public ArrayList<Rodove> select() {
	String query = this.main_select;
	return getResult(query);
    }

    public ArrayList<Rodove> selectYearAfter(int year) {
	Calendar cal = Calendar.getInstance();
	int cur_year = cal.get(Calendar.YEAR);

	String query = this.main_select + " WHERE " + cur_year + "-year_start"
		+ "<"+year;
	
	return getResult(query);
    }

    private ArrayList<Rodove> convertResultToRodove(ResultSet res) {
	ArrayList<Rodove> arr = new ArrayList<Rodove>();

	try {
	    while (res.next()) {
		Rodove item = new Rodove();

		item.setId(res.getInt("id"));
		item.setName(res.getString("name"));
		item.setPlace(res.getString("place"));
		item.setSkhema(res.getString("skhema"));
		item.setYear_start(res.getInt("year_start"));
		item.setYear_used(res.getInt("year_used"));
		item.setOblast(res.getString("oblast"));
		item.setRegion(res.getString("region"));
		item.setOil_size(res.getString("oil_size"));
		item.setGas_size(res.getString("gas_size"));
		item.setType(res.getString("rodowe_type"));
		arr.add(item);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return arr;
    }

    // get run query to DB and covert data to human
    private ArrayList<Rodove> getResult(String query) {
	ResultSet res = this.db.select(query);

	ArrayList<Rodove> arr = new ArrayList<Rodove>();
	arr = convertResultToRodove(res);
	return arr;
    }

}
